package com.szholly.pro.watergas_server.ps.schedule;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.RowBase;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.ps.service.DataReportService;
import com.szholly.pro.watergas_server.ps.service.WaterQualityAnalysisService;
import com.szholly.utils.spring.SpringBeanFactory;
import org.apache.log4j.Logger;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;

import lrq.common.MyLog;
/**
 *  定时任务，每天定时生成日、 周、月、季、年的综合污染指数数据
 * @author 熊伟
 *
 */
public class CreateCompPollIndexData  implements Job {
	static Logger log;
	
	@Autowired
	DataReportService dataReportService;
	
	@Autowired
	WaterQualityAnalysisService waterQualityAnalysisService;
	@Override
	public void execute(JobExecutionContext arg0) throws JobExecutionException {
		
		if(log == null){
			log = MyLog.getLogger("CreatePollIndexData");
		}
		 
		try {
		   Calendar cal=Calendar.getInstance();
		   Calendar realCal = Calendar.getInstance();
		   
		   //生成综合污染指数日数据
		   realCal.setTime(cal.getTime());  
		   CreateDayCompPollIndex(realCal);
		   //生成综合污染指数周数据
		   realCal.setTime(cal.getTime());  
		   CreateWeekCompPollIndex(realCal);
	       //生成综合污染指数月数据
	       realCal.setTime(cal.getTime());  
	       CreateMonthCompPollIndex(realCal);  
	       //生成综合污染指数季数据
	       realCal.setTime(cal.getTime());  
	       CreateQuarterCompPollIndex(realCal);
	       //生成综合污染指数年数据
	       realCal.setTime(cal.getTime());  
	       CreateYearCompPollIndex(realCal);
		   
		} catch (Exception e) {
			log.error("生成综合污染指数数据出错",e);
		}
	}
	
	/**
	 * 
	 * 根据monitor_data_day中的均值数据，生成并插入相应的综合污染指数数据（monitor_data_day,污染物参数为comp_poll_index）
	 * xwei
	 * 2017年05月17日
	 * @return
	 * @throws ParseException 
	 */
	public void CreateDayCompPollIndex(Calendar cal) throws ParseException {
//        //获取开始时间与结束时间
		try {
			Date endTime = cal.getTime();
			cal.add(Calendar.DAY_OF_MONTH, -1);
			Date startTime = cal.getTime();	       
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String strStartTime = sdf.format(startTime);
			String strEndTime = sdf.format(endTime);
			String tableName = "monitor_data_day";
			String waterTypeTableName = "day_water_type";
			CreatePollIndexData(sdf.parse(strStartTime),sdf.parse(strEndTime),tableName,waterTypeTableName);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 
	 * 根据monitor_data_week中的均值数据，生成并插入相应的综合污染指数数据（monitor_data_week,污染物参数为comp_poll_index）
	 * xwei
	 * 2017年05月17日
	 * @return
	 * @throws ParseException 
	 */
	private void CreateWeekCompPollIndex(Calendar cal){
		log.info("开始生成周数据");
		try{
			//判断当前时间是否为周一
	        if(cal.get(Calendar.DAY_OF_WEEK) != Calendar.MONDAY)
	        {
	        	return;
	        }
	        
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.DAY_OF_MONTH, -7);
			Date startTime = cal.getTime();
			String tableName = "monitor_data_week";
			String waterTypeTableName = "day_water_week";
			//设置周一为一周的开头,week=1时，应设置为53周
			cal.setFirstDayOfWeek(Calendar.MONDAY);
			int week = cal.get(Calendar.WEEK_OF_YEAR);
			if(week == 1)
			{
				week = 53;
			}
			CreatePollIndexData(startTime,endTime,tableName,waterTypeTableName);
		} catch(Exception e)
		{
			log.error("生成周数据出错",e);
		}
	}
	
	
	/**
	 * 
	 * 根据monitor_data_month中的均值数据，生成并插入相应的综合污染指数数据（monitor_data_month,污染物参数为comp_poll_index）
	 * xwei
	 * 2017年05月17日
	 * @return
	 * @throws ParseException 
	 */
	private void CreateMonthCompPollIndex(Calendar cal){
		log.info("开始生成月数据");
		try{
			//判断当前时间是否为1号
	        if(cal.get(Calendar.DAY_OF_MONTH) != 1)
	        {
	        	return;
	        }
	        
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.MONTH, -1);
			Date startTime = cal.getTime();
			String tableName = "monitor_data_week";
			String waterTypeTableName = "week_water_type";
			CreatePollIndexData(startTime,endTime,tableName,waterTypeTableName);
		} catch(Exception e)
		{
			log.error("生成月数据出错",e);
		}
	}
	
	/**
	 * 
	 * 根据monitor_data_quarter中的均值数据，生成并插入相应的综合污染指数数据（monitor_data_quarter,污染物参数为comp_poll_index）
	 * xwei
	 * 2017年05月17日
	 * @return
	 * @throws ParseException 
	 */
	private void CreateQuarterCompPollIndex(Calendar cal){
		log.info("开始生成季数据");
		try{
			//判断当前时间是否为1号,是否为季度的第一个月
	        if(cal.get(Calendar.DAY_OF_MONTH) != 1 || (cal.get(Calendar.MONTH)%3)!=0)
	        {
	        	return;
	        }
	             
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.MONTH, -3);
			Date startTime = cal.getTime();
	       
			String tableName = "monitor_data_quarter";
			String waterTypeTableName = "quarter_water_type";
			CreatePollIndexData(startTime,endTime,tableName,waterTypeTableName);
		} catch(Exception e)
		{
			log.error("生成季数据出错",e);
		}
	}
	
	
	/**
	 * 
	 * 根据monitor_data_year中的均值数据，生成并插入相应的综合污染指数数据（monitor_data_year,污染物参数为comp_poll_index）
	 * xwei
	 * 2017年05月17日
	 * @return
	 * @throws ParseException 
	 */
	private void CreateYearCompPollIndex(Calendar cal){
		log.info("开始生成年数据");
		try{
			//判断当前时间当年第一天
	        if(cal.get(Calendar.DAY_OF_YEAR) != 1)
	        {
	        	return;
	        }
	           
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.YEAR, -1);
			Date startTime = cal.getTime();
	       
			String tableName = "monitor_data_year";
			String waterTypeTableName = "year_water_type";
			CreatePollIndexData(startTime,endTime,tableName,waterTypeTableName);
		} catch(Exception e)
		{
			log.error("生成年数据出错",e);
		}
	}
	
	
	/**
	 * 生成数据
	 * xwei
	 * 2017年051月17日
	 */
	protected void CreatePollIndexData(Date startTime,Date endTime,String tableName,String waterTypeTableName)throws Exception{
		/*1、根据开始结束日期获取计算综合污染指数的五个污染物数据。
		2、查询地表水系统所有的站点以及对应的参数。
		3、查询综合污染指数的标准。
		4、根据五个参数值进行综合污染指数的计算。
		5、保存综合污染指到数据库。*/
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			String parmSql = "select mn,parmname,avg,datatime,date_text from "+tableName+" t1,\n"
					+" monitor_station t2,BC_COLLECTION_INFO t3 "
					+" where t2.code= t3.site_id\n"
					+" and t3.device_num= t1.mn\n"
					+" and t2.STATION_CATEGORY='1' \n"
					+" and datatime >= @startDate\n"
					+" and datatime <= @endDate\n"
					+" and parmname in ('001','011','060','101','do','phtem')\n"
					+"group by mn,parmname,avg,datatime,date_text order by mn,parmname, datatime desc";
			String stationSql = "select mn from "+tableName+" t1,\n"
					+" monitor_station t2,BC_COLLECTION_INFO t3 "
					+" where t2.code= t3.site_id\n"
					+" and t3.device_num= t1.mn\n"
					+" and t2.STATION_CATEGORY='1' \n"
					+" and datatime >= @startDate\n"
					+" and datatime <= @endDate\n"
					+" and parmname in ('001','011','060','101','do')\n"
					+"group by mn order by mn desc";
			String timeSql = "select distinct trunc(datatime) time,date_text from "+tableName+" t1,\n"
					+" monitor_station t2,BC_COLLECTION_INFO t3 "
					+" where t2.code= t3.site_id\n"
					+" and t3.device_num= t1.mn\n"
					+" and t2.station_type='1' \n"
					+" and datatime >= @startDate\n"
					+" and datatime <= @endDate\n"
					+" and parmname in ('001','011','060','101','do')\n"
					+"group by datatime,date_text  order by time desc";
			List<GlobalParameter> pc = new ArrayList<>();
			ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
			ITable pTable = tableFactory.openTable("POLLDATA");
			pc.add(new GlobalParameter("startDate", startTime));
			pc.add(new GlobalParameter("endDate", endTime));
			
			List<RowBase> queryParmValueList = DataUtil.getSelectRows(parmSql, pc,pTable);
			List<RowBase> stationsList = DataUtil.getSelectRows(stationSql, pc,pTable);
			List<RowBase> timeList = DataUtil.getSelectRows(timeSql, pc,pTable);
			List<RowBase> compPollIndex = computeCompPollIndex(queryParmValueList,timeList,stationsList);
			
			for(int k =0;k<compPollIndex.size();k++) {
				RowBase row = new RowBase();
				row = compPollIndex.get(k);
				
				String MN = row.getSafeString("mn");
				String date_text = row.getSafeString("DATATEXT");
				String pollIndex = row.getSafeString("compPollIndex");
				String dataTime = row.getSafeString("datatime")+" 01:00:00";
				String insertSql="";
				if("monitor_data_day".equals(tableName)) {
					insertSql = "insert into " +tableName+"(guid, qn, st, cn, datatime, mn, cou, max, min, avg, parmname, "
							+ "createtime, flag, error_data, amount_dens, amount_cou, msg, modify_user_id, modify_date, modify_certificate_id, data_from, date_text) "
							+ "values (sys_guid(),'','','',@datatime,@mn,'','','',@value,@parmname,sysdate,'','','','','','','','','',@date_text)";

				} else {
					insertSql = "insert into " +tableName+"(guid, datatime, mn, cou, max, min, avg, parmname, date_text) "
							+ "values (sys_guid(),@datatime,@mn,'','','',@value,@parmname,@date_text)";
				}
				
				List<GlobalParameter> parms = new ArrayList<>();
				parms.add(new GlobalParameter("parmname", "COMP_POLL_INDEX"));
				parms.add(new GlobalParameter("mn", MN));
				parms.add(new GlobalParameter("value", pollIndex));
				parms.add(new GlobalParameter("datatime", sdf.parse(dataTime)));
				parms.add(new GlobalParameter("date_text", date_text));
				//综合污染指数数据入库
				DataUtil.ExecuteUpdateSql(insertSql, parms);	
			}				
	}
	/**
	 * 获取水质类别行
	 * @param avgrow 均值
	 * @param standardRows 标准值
	 * @return
	 * @throws ParseException 
	 */
	private List<RowBase> computeCompPollIndex (List<RowBase> avgrow,List<RowBase> time,List<RowBase> stations) throws ParseException {
	/*目前只计算五个指标的综合污染指数；其中，PH和DO(溶解氧)的计算公式不一样*/		
		/*首先计算COD,氨氮，总磷各自的污染分指数Pi。*/
		double compPollIndex = 0.0;
		double COD_Pi = 0.0;
		double NH4_Pi = 0.0;
		double P_Pi = 0.0;
		double PH_Pi =0.0;
		double Do_Pi = 0.0;
		double tem = 0.0;
		List<RowBase> roww = new ArrayList<RowBase>(stations.size()*time.size());
		
		for(int j =0;j<stations.size();j++) {
			RowBase rr = new RowBase();
			rr = stations.get(j);
			String MN = rr.getSafeString("MN");
			for(int i =0;i<time.size();i++) {
				RowBase row  = new RowBase();
				row = time.get(i);
				String datetime = row.getSafeString("TIME");
				datetime=datetime.substring(0, 10);
				String dataText = row.getSafeString("date_text");
				Double Doj = null;
				//单独获取溶解氧的实测值
				for(int k=0;k < avgrow.size(); k++) {
					RowBase r  = new RowBase();
					r = avgrow.get(k);
					String parmname = r.getSafeString("parmname");
					if("do".equals(parmname)){
						Doj = r.getDouble("avg");
						break;
					}		
				}
				
				for(int k=0;k < avgrow.size(); k++) {
					RowBase r  = new RowBase();
					r = avgrow.get(k);
					String times = r.getSafeString("datatime");
					times=times.substring(0, 10);
					String mn = r.getSafeString("mn");
					if(mn.equals(MN)&&times.equals(datetime)) {
						double avgValue = r.getDouble("avg");
						String parmname = r.getSafeString("parmname");
						
							if("011".equals(parmname)){//浓度
								COD_Pi = avgValue/40;
							} else if("060".equals(parmname)){//浓度
								NH4_Pi = avgValue/2;
							} else if("101".equals(parmname)){//浓度
								P_Pi = avgValue/2;
							} else if ("001".equals(parmname)) {
								if(avgValue>7.0) {
									PH_Pi = (avgValue-7)/2;
								} else if(avgValue<=7.0){
									PH_Pi = 7.0-avgValue;
								}
							} else if ("phtem".equals(parmname)&&Doj!=null) {
								
								tem = r.getDouble("avg");
								double DOf = 468/(31.6+tem);
								double DOs = 2.0;
								double doj = Doj.doubleValue();
								if(doj>=DOs) {
									Do_Pi = Math.abs(DOf-doj)/(DOf-DOs);
								} else {
									Do_Pi = 10-9*(doj/DOs);
								}
							}
					}
				}
				RowBase rrr = new RowBase();
				compPollIndex = COD_Pi+NH4_Pi+P_Pi+Do_Pi+PH_Pi;
				rrr.setValue("MN",MN);
				rrr.setValue("datatime",datetime);
				rrr.setValue("dataText",dataText);
				rrr.setValue("compPollIndex",compPollIndex);
				roww.add(rrr);
			}
		}
		return roww;
	}
    
}
